<?php
/**
 * 测试客户列表查询条件修复效果
 * 对比修复前后的SQL查询结果
 */

// 数据库连接配置 (开发环境)
$config = [
    'host' => 'localhost',
    'dbname' => 'oa5', 
    'username' => 'root',
    'password' => '123456',
    'charset' => 'utf8mb4'
];

try {
    $pdo = new PDO(
        "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}", 
        $config['username'], 
        $config['password'],
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    );
    
    echo "数据库连接成功\n";
    echo str_repeat("=", 50) . "\n\n";
    
    // 检查客户ID=41的状态
    echo "=== 检查客户ID=41的状态 ===\n";
    $stmt = $pdo->prepare("SELECT id, name, delete_time FROM oa_crm_customer WHERE id = 41");
    $stmt->execute();
    $customer41 = $stmt->fetch();
    
    if ($customer41) {
        echo "客户ID=41：\n";
        echo "  - 姓名: {$customer41['name']}\n";
        echo "  - 删除时间: {$customer41['delete_time']}\n";
        echo "  - 状态: " . ($customer41['delete_time'] > 0 ? "已删除" : "未删除") . "\n";
    } else {
        echo "未找到客户ID=41\n";
    }
    
    echo "\n" . str_repeat("=", 50) . "\n\n";
    
    // 显示所有已删除的客户
    echo "=== 所有已删除的客户记录 ===\n";
    $stmt = $pdo->query("SELECT id, name, delete_time FROM oa_crm_customer WHERE delete_time > 0 ORDER BY id");
    $deletedCustomers = $stmt->fetchAll();
    
    if ($deletedCustomers) {
        echo "找到 " . count($deletedCustomers) . " 条已删除的客户记录：\n";
        foreach ($deletedCustomers as $customer) {
            echo "  - ID: {$customer['id']}, 姓名: {$customer['name']}, 删除时间: {$customer['delete_time']}\n";
        }
    } else {
        echo "没有找到已删除的客户记录\n";
    }
    
    echo "\n" . str_repeat("=", 50) . "\n\n";
    
    // 模拟修复前的错误查询逻辑
    echo "=== 模拟修复前的错误查询逻辑 ===\n";
    $stmt = $pdo->query("
        SELECT c.*, u.name as user_name 
        FROM oa_crm_customer c 
        LEFT JOIN oa_admin u ON c.user_id = u.id 
        WHERE (c.delete_time IS NULL OR c.delete_time = 0 OR c.delete_time = '')
        ORDER BY c.id DESC 
        LIMIT 10
    ");
    $oldQueryResults = $stmt->fetchAll();
    
    echo "修复前查询结果数量: " . count($oldQueryResults) . "\n";
    echo "是否包含已删除客户: ";
    
    $containsDeleted = false;
    foreach ($oldQueryResults as $customer) {
        if ($customer['delete_time'] > 0) {
            echo "是 (发现ID: {$customer['id']})\n";
            $containsDeleted = true;
            break;
        }
    }
    
    if (!$containsDeleted) {
        echo "否\n";
    }
    
    echo "\n" . str_repeat("=", 50) . "\n\n";
    
    // 模拟修复后的正确查询逻辑
    echo "=== 模拟修复后的正确查询逻辑 ===\n";
    $stmt = $pdo->query("
        SELECT c.*, u.name as user_name 
        FROM oa_crm_customer c 
        LEFT JOIN oa_admin u ON c.user_id = u.id 
        WHERE c.delete_time = 0
        ORDER BY c.id DESC 
        LIMIT 10
    ");
    $newQueryResults = $stmt->fetchAll();
    
    echo "修复后查询结果数量: " . count($newQueryResults) . "\n";
    echo "是否包含已删除客户: ";
    
    $containsDeleted = false;
    foreach ($newQueryResults as $customer) {
        if ($customer['delete_time'] > 0) {
            echo "是 (发现ID: {$customer['id']})\n";
            $containsDeleted = true;
            break;
        }
    }
    
    if (!$containsDeleted) {
        echo "否\n";
    }
    
    echo "\n" . str_repeat("=", 50) . "\n\n";
    
    // 关键词搜索测试
    if ($customer41) {
        echo "=== 关键词搜索测试 (搜索'{$customer41['name']}') ===\n";
        
        // 修复前的搜索
        $stmt = $pdo->prepare("
            SELECT id, name, delete_time 
            FROM oa_crm_customer 
            WHERE (delete_time IS NULL OR delete_time = 0 OR delete_time = '') 
            AND name LIKE ?
            ORDER BY id DESC 
            LIMIT 10
        ");
        $stmt->execute(["%{$customer41['name']}%"]);
        $oldSearchResults = $stmt->fetchAll();
        
        echo "修复前搜索结果: " . count($oldSearchResults) . " 条记录\n";
        foreach ($oldSearchResults as $result) {
            echo "  - ID: {$result['id']}, 姓名: {$result['name']}, delete_time: {$result['delete_time']}\n";
        }
        
        echo "\n";
        
        // 修复后的搜索
        $stmt = $pdo->prepare("
            SELECT id, name, delete_time 
            FROM oa_crm_customer 
            WHERE delete_time = 0 
            AND name LIKE ?
            ORDER BY id DESC 
            LIMIT 10
        ");
        $stmt->execute(["%{$customer41['name']}%"]);
        $newSearchResults = $stmt->fetchAll();
        
        echo "修复后搜索结果: " . count($newSearchResults) . " 条记录\n";
        foreach ($newSearchResults as $result) {
            echo "  - ID: {$result['id']}, 姓名: {$result['name']}, delete_time: {$result['delete_time']}\n";
        }
    }
    
    echo "\n" . str_repeat("=", 50) . "\n";
    echo "测试完成！\n";
    echo "修复效果总结：\n";
    echo "- 修复前: 错误的使用WHERE OR逻辑，可能包含已删除记录\n";
    echo "- 修复后: 正确的使用WHERE delete_time = 0，只返回未删除记录\n";
    
} catch (PDOException $e) {
    echo "数据库连接失败: " . $e->getMessage() . "\n";
}
?>